import pymysql
import pandas as pd
import re
import numpy as np


# 打开数据库连接
db = pymysql.connect(
                     host="localhost",
                     user="root",
                     password="hui123456",
                     db='dbtest'
                     )
cursor = db.cursor()

# 抖音平台SQL查询
sql = """
        SELECT DISTINCT Product_BianMa, COUNT(*)  
        FROM doudian_day 
        WHERE month(day) = 9 
        GROUP BY Product_BianMa
"""

# 天猫等平台SQL查询
sql1 = """
        SELECT DISTINCT Product_BianMa, COUNT(*)  
        FROM niandu_day 
        WHERE month(day) = 9 
        GROUP BY Product_BianMa
"""



cursor.execute(sql)
result_ = cursor.fetchall()
result_d = pd.DataFrame(list(result_), columns=['商家编码', '个数'])

cursor.execute(sql1)
result_tm = cursor.fetchall()
result_tmm = pd.DataFrame(list(result_tm), columns=['商家编码', '个数'])

result = pd.concat([result_d, result_tmm])

# 读取匹配表
pp = pd.read_excel(r'G:\工作\商家编码匹配产品 -4月财务.xlsx')

# 分列处理
s_1 = []  # 分列后以列表形式保存
r_1 = []  # 添加每个分列后的产品
r_5 = []  # 添加SKU

for date_s in list(result['商家编码']):
    if '+' in date_s:
        date_split = str(date_s).split('+')
        s_1.append(date_split)
    elif '，' in date_s:
        date_split = str(date_s).split('，')
        if '赠' in date_split[0]:
            date_split_1 = date_split[0].split('赠')
            s_1.append(date_split_1)
        else:
            s_1.append([date_split[0]])
    elif '赠' in date_s:
        date_split = str(date_s).split('赠')
        s_1.append(date_split)
    elif '＋' in date_s:
        date_split = str(date_s).split('＋')
        s_1.append(date_split)
    else:
        s_1.append([date_s])

result['编码分裂'] = s_1
date_concat_s = result.reset_index(drop=True)

n = 0
for i in list(date_concat_s['编码分裂']):
    if len(i) == 1:
        r_1.append(i[0])
        r_5.append(list(date_concat_s['商家编码'])[n])
    else:
        for c in i:
            r_1.append(c)
            r_5.append(list(date_concat_s['商家编码'])[n])
    n += 1

s = {'SKU': r_5, '商家编码': r_1}
res = pd.DataFrame(s)

res_pp = pd.merge(res, pp, on=['商家编码'], how='left')

# 提取商品袋数
def d(x):
    if '袋' in x:
        try:
            return re.findall(r"(\d+)袋", x)[-1]
        except:
            return 0
    elif '桶' in x:
        try:
            return re.findall(r"(\d+)桶", x)[-1]
        except:
            return 0
    elif '个' in x:
        try:
            return re.findall(r"(\d+)个", x)[-1]
        except:
            return 0
    elif '包' in x:
        try:
            return re.findall(r"(\d+)包", x)[-1]
        except:
            return 0
    elif '只' in x:
        try:
            return re.findall(r"(\d+)只", x)[-1]
        except:
            return 0
    elif '根' in x:
        try:
            return re.findall(r"(\d+)根", x)[-1]
        except:
            return 0
    else:
        return 0

res_pp['件数'] = res_pp['商家编码'].map(d)
res_pp['件数'] = res_pp['件数'].astype(int)
res_pp['件数1'] = np.where(res_pp['单位'] == '总', 1, res_pp['件数'])
res_pp['成本'] = res_pp['件数1'] * res_pp['成本价']

res_pp.to_excel(r'G:\工作\SKU利润测算\2025年\9月\9月SKU成本全平台--财务.xlsx',
                            index=False)
